package com.pro.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import com.app.util.PropertiesUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class DbUtil {

	private String dbUrl=PropertiesUtil.getValue("db.url");
	private String dbUserName=PropertiesUtil.getValue("db.username");
	private String dbPassword=PropertiesUtil.getValue("db.password");
	private String jdbcName="com.mysql.jdbc.Driver";
	
	private static DbUtil dbutil = new DbUtil();
	
	public Connection getCon()throws Exception{
		Class.forName(jdbcName);
		Connection con=DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
		return con;
	}
	
	private DbUtil(){
		
	}
	
	public static DbUtil getInstance(){
		if(dbutil == null){
			return new DbUtil();
		}
		return dbutil;
	}
	
	public void closeCon(Connection con)throws Exception{
		if(con!=null){
			con.close();
		}
	}
	
	/**
	 * ��ȡ��ݿ�����
	 * @return
	 */
	public  Connection getConnection(){
		
		try {
			return getCon();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * �ر�����
	 * @param connection
	 * @param statement
	 * @param rs
	 */
	public  void closeConnection(Connection connection,PreparedStatement statement,ResultSet rs){
		try {
			if(rs!=null)rs.close();
			if(statement!=null)statement.close();
			if(connection!=null)connection.close();
		} catch (Exception e) {
			e.fillInStackTrace();
		}
	}
	
	
	/**
	 * ��ѯ����ݣ�����list����
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public  List<Map<String,Object>> queryForList(String sql,Object...objects){
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		Connection connection = getConnection();
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				statement.setObject(i+1, objects[i]);
			}
			
			rs = statement.executeQuery();
			while(rs.next()){
				ResultSetMetaData resultSetMetaData = rs.getMetaData();
				int count = resultSetMetaData.getColumnCount(); //��ȡ����
				Map<String,Object> map = new HashMap<String, Object>();
				for (int i = 0; i < count; i++) {
					map.put(resultSetMetaData.getColumnName(i+1), rs.getObject(resultSetMetaData.getColumnName(i+1)));
				}
				result.add(map);
			};
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			closeConnection(connection, statement, rs);
		}
		
		return result;
	}
	
	/**
	 * ��ѯ����ݣ�����map����
	 * @param sql
	 * @param objects
	 * @return
	 * @throws SQLException
	 */
	public  Map<String,Object> queryForMap(String sql,Object...objects) throws SQLException{
		Map<String,Object> result = new HashMap<String,Object>();
		List<Map<String,Object>> list = queryForList(sql, objects);
		if(list.size() != 1){
			return null;
		}
		result = list.get(0);
		return result;
	}
	
	/**
	 * ��ѯ����ݣ����ҷ���һ��JavaBean
	 * @param sql
	 * @param clazz
	 * @param objects
	 * @return
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 */
	public  <T>T queryForBean(String sql,Class clazz,Object...objects){
		T obj = null;
		Map<String,Object> map = null;
		Field field = null;
		try {
			obj = (T) clazz.newInstance();   //����һ���µ�Beanʵ��
			map = queryForMap(sql, objects); //�Ƚ�������һ��Map��
		} catch (Exception e) {
			e.printStackTrace();
		} 
		if(map == null){
			return null;
		}
		//����Map
		for (String columnName : map.keySet()) {
			Method method = null;
			String propertyName = StringUtils.columnToProperty(columnName); //�������
			
			try {
				field = clazz.getDeclaredField(propertyName);
			} catch (NoSuchFieldException e1) {
				e1.printStackTrace();
			} catch (SecurityException e1) {
				e1.printStackTrace();
			} 
			//��ȡJavaBean�е��ֶ�
			String fieldType = field.toString().split(" ")[1]; //��ȡ���ֶε�����
			//System.out.println(fieldType);
			Object value = map.get(columnName);
			if(value == null){
				continue;
			}
			/**��ȡset����������* */
			String setMethodName = "set" + StringUtils.upperCaseFirstCharacter(propertyName);
			//System.out.println(setMethodName);
			try {
				/**��ȡֵ������* */
				String valueType = value.getClass().getName();
				
				/**�鿴�����Ƿ�ƥ��* */
				if(!fieldType.equalsIgnoreCase(valueType)){
					//System.out.println("���Ͳ�ƥ��");
					if(fieldType.equalsIgnoreCase("java.lang.Integer")){
						value = Integer.parseInt(String.valueOf(value));
					}else if(fieldType.equalsIgnoreCase("java.lang.String")){
						value = String.valueOf(value);
					}else if(fieldType.equalsIgnoreCase("java.util.Date")){
						valueType = "java.util.Date";
						//��valueת����java.util.Date
						String dateStr = String.valueOf(value);
						Timestamp ts = Timestamp.valueOf(dateStr);
						Date date = new Date(ts.getTime());
						value = date;
					}
				}
				
				/**��ȡset����* */
				//System.out.println(valueType);
				method = clazz.getDeclaredMethod(setMethodName,Class.forName(fieldType));
				/**ִ��set����* */
				method.invoke(obj, value);
			}catch(Exception e){
				/**���?��������Ϊ���Ͳ�ƥ��* */
				e.printStackTrace();
			}
		}
		//System.out.println(obj);
		return obj;
	}
	
	
	/**
	 * DML����
	 * @param sql
	 * @param objects
	 * @return 
	 */
	public  int update(String sql,Object...objects){
		Connection connection = getConnection();
	    PreparedStatement statement = null;
	    try {
	    	statement = (PreparedStatement) connection.prepareStatement(sql);
	    	for (int i = 0; i < objects.length; i++) {
				statement.setObject(i+1, objects[i]);
			}
	    	statement.executeUpdate();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }finally{
	    	closeConnection(connection, statement, null);
	    }
		return 0;
	}
	
	
	
	public JSONObject queryForJsonObject(String sql, Object... objects) throws SQLException {
        Map map = this.queryForMap(sql, objects);
        return map == null ? null : JSONObject.fromObject(map);
    }

    public JSONArray queryForJsonArray(String sql, Object... objects) {
        return JSONArray.fromObject(this.queryForList(sql, objects));
    }
    
    
    public List<String> getColumeList(String table_schema, String table_name) {
        String sql_forColumnName = "select COLUMN_NAME from information_schema.columns where table_schema = ? and table_name = ?";
        List cloumnList = this.queryForcloumnList(sql_forColumnName, "COLUMN_NAME",
                new Object[]{table_schema, table_name});
        return cloumnList;
    }
    
    
    public List<String> getColumeList(String table_name) {
        String sql_forColumnName = "select COLUMN_NAME from information_schema.columns where table_name = ?";
        List cloumnList = this.queryForcloumnList(sql_forColumnName, "COLUMN_NAME",
                new Object[]{table_name});
        return cloumnList;
    }
    
    public List<String> getCommentList(String table_name) {
        String sql_forColumnName = "select COLUMN_COMMENT from information_schema.columns where table_name = ?";
        List cloumnList = this.queryForcloumnList(sql_forColumnName, "COLUMN_COMMENT",
                new Object[]{table_name});
        return cloumnList;
    }
    
    public List<String> getCommentList(String table_schema,String table_name) {
    	String sql_forColumnName = "select COLUMN_COMMENT from information_schema.columns where table_schema = ? and table_name = ?";
    	List cloumnList = this.queryForcloumnList(sql_forColumnName, "COLUMN_COMMENT",
    			new Object[]{table_schema,table_name});
    	return cloumnList;
    }

    public int update(String table_schema, String table_name, Map<String, Object> paramMapper, String keyColumn) {
        List cloumnList = this.getColumeList(table_schema, table_name);
        ArrayList listValues = new ArrayList();
        if (cloumnList.size() < 1) {
            try {
                throw new Exception("这张表没有一个字段或者表不存在！");
            } catch (Exception arg10) {
                arg10.printStackTrace();
                return 0;
            }
        } else if (paramMapper == null) {
            try {
                throw new Exception("需要修改的map不能为空！");
            } catch (Exception arg11) {
                arg11.printStackTrace();
                return 0;
            }
        } else if (keyColumn != null && paramMapper.get(keyColumn) != null) {
            String sql_forUpdate = "update " + table_name + " set ";
            byte count = 0;
            Iterator arg9 = cloumnList.iterator();

            while (arg9.hasNext()) {
                String rs = (String) arg9.next();
                if (!rs.equals(keyColumn) && StringUtils.isNotEmpty(paramMapper.get(rs))) {
                    ++count;
                    listValues.add(paramMapper.get(rs));
                    sql_forUpdate = sql_forUpdate + rs + "=" + "?,";
                }
            }

            if (count == 0) {
                try {
                    throw new Exception("请核实需要保存的字段！");
                } catch (Exception arg12) {
                    arg12.printStackTrace();
                    return 0;
                }
            } else {
                sql_forUpdate = sql_forUpdate.substring(0, sql_forUpdate.length() - 1);
                sql_forUpdate = sql_forUpdate + " where " + keyColumn + "=?";
                listValues.add(paramMapper.get(keyColumn));
                System.out.println(sql_forUpdate);
                int arg14 = this.update(sql_forUpdate, StringUtils.listToObjectArray(listValues));
                return arg14;
            }
        } else {
            try {
                throw new Exception("请设置修改行的匹配列！");
            } catch (Exception arg13) {
                arg13.printStackTrace();
                return 0;
            }
        }
    }

    public int save(String table_schema, String table_name, Map<String, Object> paramMapper) {
        List cloumnList = this.getColumeList(table_schema, table_name);
        ArrayList listValues = new ArrayList();
        if (cloumnList.size() < 1) {
            try {
                throw new Exception("这张表没有一个字段或者表不存在！");
            } catch (Exception arg9) {
                arg9.printStackTrace();
                return 0;
            }
        } else if (paramMapper == null) {
            try {
                throw new Exception("需要保存的map不能为空！");
            } catch (Exception arg10) {
                arg10.printStackTrace();
                return 0;
            }
        } else {
            String sql_forInsert = "insert into " + table_name + "(";
            sql_forInsert = sql_forInsert + StringUtils.join(cloumnList) + ") values (";
            byte count = 0;
            Iterator arg8 = cloumnList.iterator();

            while (arg8.hasNext()) {
                String rs = (String) arg8.next();
                if (StringUtils.isNotEmpty(paramMapper.get(rs))) {
                    ++count;
                    listValues.add(paramMapper.get(rs));
                    sql_forInsert = sql_forInsert + "?,";
                } else {
                    sql_forInsert = sql_forInsert + "NULL,";
                }
            }

            if (count == 0) {
                try {
                    throw new Exception("请核实需要保存的字段！");
                } catch (Exception arg11) {
                    arg11.printStackTrace();
                    return 0;
                }
            } else {
                sql_forInsert = sql_forInsert.substring(0, sql_forInsert.length() - 1) + ")";
                this.update(sql_forInsert, StringUtils.listToObjectArray(listValues));
                return 0;
            }
        }
    }

    public List<String> queryForcloumnList(String sql, String columnName, Object... objects) {
        JSONArray jsonArray = this.queryForJsonArray(sql, objects);
        ArrayList resultList = new ArrayList();

        for (int i = 0; i < jsonArray.size(); ++i) {
            JSONObject json = (JSONObject) jsonArray.get(i);
            resultList.add(json.getString(columnName));
        }

        return resultList;
    }

    public String queryForString(String sql, Object... objects) throws SQLException {
        JSONObject jsonObject = this.queryForJsonObject(sql, objects);
        Iterator sIterator = jsonObject.keys();
        if (sIterator.hasNext()) {
            String key = (String) sIterator.next();
            String value = jsonObject.getString(key);
            return value;
        } else {
            return null;
        }
    }

    public int queryForInt(String sql, Object... objects) throws NumberFormatException, SQLException {
    	try{
    		return Integer.parseInt(this.queryForString(sql, objects));
    	}catch(Exception e){
    		return 0;
    	}
        
    }

    public long queryForLong(String sql, Object... objects) throws NumberFormatException, SQLException {
        return Long.parseLong(this.queryForString(sql, objects));
    }
    
    
    
    
    
    
    
    
    
    
    
	
	public  void main(String[] args) {
		DbUtil dbUtil=new DbUtil();
		try {
			dbUtil.getCon();
			System.out.println("��ݿ����ӳɹ���");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("��ݿ�����ʧ�ܣ�");
		}
	}
}
